import json
import requests
import pymysql

# 协同平-安全管理-航运公司安全管理系统-体系审核-审核实施-审核材料-船舶审核
BASE_URL = "http://cmp.msa.gov.cn/ShippingSecurity/Examine/Examine_Local.action"
# 审核材料详细 - 审核人
person_url = "http://cmp.msa.gov.cn/ShippingSecurity/Scheme/Scheme_Local.action"
#审核详细
data_url = "http://cmp.msa.gov.cn/ShippingSecurity/ServiceBC/Scheme_getSchemeObject.action"
#不符合清单
desc_url = "http://cmp.msa.gov.cn/ShippingSecurity/ServiceBC/SchemeShipIncf_getList.action"
#问题
question_url = "http://cmp.msa.gov.cn/ShippingSecurity/ServiceBC/SchemeShipInv_getList.action"

headers = {
    'Accept': '*/*',
    # Content-Type设置为application/json，接口才能返回json数据
    'Content-Type': 'application/x-www-form-urlencoded; charset=UTF-8'
}
cookies = {
    # OAMAuthnCookie_cmp.msa.gov.cn:80 参数用于登录认证
    'OAMAuthnCookie_cmp.msa.gov.cn:80': 'EDAdKMydXbvWxt%2F6bSrSc46o%2FQQoOnUfUPmcMSH5hGijBKriMnFJkvg7ofohl%2FxsgOJtSyh4RMqrW1z%2BvaOr%2BSYIwZm4k5syz%2Bv2j9Vx1sdxaifs4goeGNrxZj8PHq39uNs4ndGwnbNqdTsHvs0GO7%2FaHajSru0kbxXp7AI5hEVl6la%2FLP%2FOdcSdp5Y8cYWOVm8Mss%2FDp%2F3mlCZuH5oOoe%2BBmj7nn68mnfbgg6%2FxEepzGZgWLLQL6q3fH6uqSG78fmzMhp0raG56Vg0J9UvilEx95OyRQ2basSiltaIaOUjLLR5YqGXLHfo8AadmY3DYOjLtoteQzLKYLG1tGpUdmxudlZjFBT%2FlCOD5hTafgwL1aqz%2BNJuhk5uZv4kWUvTuCdvxPIiuT1HSc72trpsn3id2cGOIF6W%2FHXi9wcNQWEF0ONgfTf12kEwXSCeR7Ckg1eaXKz13v10NBN73Ij02wBZeAKkx4kuX5babNseQBOo%3D',
    # OAMAuthnHintCookie和JSESSIONID 参数用于声明返回接口数据，不配置的会一直返回HTML
    'OAMAuthnHintCookie': '1',
    'JSESSIONID': 'wbaISYvtbfgcNPyu0AftR1xpljrSM96LYI4AExvL-ZyHuqczu3eC'
}

session = requests.Session()
# 数据库连接配置
db_config = {
    'host': '191.254.8.220',
    'user': 'root',
    'password': 'Szmsa@520',
    'db': 'spiders',
    'charset': 'utf8mb4',
    'cursorclass': pymysql.cursors.DictCursor
}
# 表名和字段名
table_name = 'xt_safety_ship_company_audit'
person_table_name = 'xt_safety_ship_company_audit_person'
question_table_name='xt_safety_ship_company_audit_question'
list_table_name='xt_safety_ship_company_audit_list'
# 连接数据库
connection = pymysql.connect(**db_config)

# 插入不符合明细
def insertChild(scheme_ship_id,scheme_id,cursor,desc_url,table_name):
    print("接口URL:"+str(desc_url))
    response_data = session.get(desc_url+'?JsonValues={"table":{"tableName":"Pub_Query","row":{"SchemeShipId":"'+scheme_ship_id+'"}}}' ,headers=headers, cookies=cookies)
    data_text = json.loads(json.loads(response_data.text))
    if data_text.get('datas') is not None:
        datas = data_text['datas']['table']['row']
        print(datas)
        # 如果审核人列表为空
        if datas is not None:
            list = datas
        else:
            list = []  # 如果结构不符合预期，设置为空列表
        # 获取的成功状态
        if response_data.status_code == 200:
            for auditor in list:
                if auditor.get('table') is not None:
                    del auditor['table']
                # 获取需要插入的字段名称
                auditor_fields = ', '.join(auditor.keys())
                # 拼装需要插入字段对应的%号个数
                auditor_values = ", ".join(['%s'] * len(auditor))
                insert_auditor = (
                    "INSERT INTO `{table}` ({fields},SchemeId) "
                    "VALUES ({values},'{schemeId}')"
                ).format(
                    table=table_name,
                    fields=auditor_fields,
                    values=auditor_values,
                    schemeId=scheme_id
                )
                cursor.execute(insert_auditor, tuple(auditor.values()))


try:
    # 获取任务状态已办结、证书制作
    data = {
        'jsonValues': '{"table":{"tableName":"Pub_query","row":{"table":[{"tableName":"Pub_Org","row":[{"OrgCode":"140000"},{"OrgCode":"140100"},{"OrgCode":"140200"},{"OrgCode":"140300"},{"OrgCode":"140400"},{"OrgCode":"140500"},{"OrgCode":"140600"}]},{"tableName":"Pub_Node","row":[{"NodeCode":"CS050122"},{"NodeCode":"CS050123"},{"NodeCode":"CS050128"},{"NodeCode":"CS050121"}]}],"CurrentPage":"1","PageRecord":"300","DateType":"3","StartApplyDate":"2024-10-01 00:00:00","EndApplyDate":"2024-12-31 23:59:59","StartDateType":"3","IfCom":"0"}}}',
        'methodValue': 'getList'
    }
    try:
        response_lo = session.post(BASE_URL, data=data, headers=headers, cookies=cookies)
        print(response_lo.text)
        data_json = json.loads(json.loads(response_lo.text))
        datas = data_json['datas']['table']['row']['table']['row']
        print(datas)
        if response_lo.status_code == 200:
            # 开始事务
            with connection.cursor() as cursor:
                for record in datas:
                    # 获取需要插入的字段名称
                    fields = ', '.join(record.keys())
                    # 拼装需要插入字段对应的%号个数
                    values = ", ".join(['%s'] * len(record))
                    scheme_id = record['SchemeId']

                    # 构建插入语句
                    insert_stmt = (
                        "INSERT INTO `{table}` ({fields},IfCom) "
                        "VALUES ({values},{IfCom})"
                    ).format(
                        table=table_name,
                        fields=fields,
                        values=values,
                        IfCom=str(0)
                    )
                    # 执行sql语句插入数据，record.values()用于获取字段中的值
                    cursor.execute(insert_stmt, tuple(record.values()))

                    # 获取审核人
                    person_param = {
                        'JsonValues': '{"table": {"tableName": "Pub_Query","row": {"SchemeId": "' + scheme_id + '"}}}',
                        'MethodValue': 'getSchemeAssessor'
                    }

                    person_data = session.post(person_url, data=person_param, headers=headers, cookies=cookies)
                    person_data_json = json.loads(json.loads(person_data.text))
                    print('人员明细：' + str(person_data_json))
                    if person_data_json.get('datas') is not None:
                        person_datas = person_data_json['datas']['table']['row']

                        if person_data.status_code == 200:
                            for person in person_datas:
                                person_fields = ', '.join(person.keys())
                                # 拼装需要插入字段对应的%号个数
                                person_values = ", ".join(['%s'] * len(person))
                                # 构建插入语句
                                person_insert_stmt = (
                                    "INSERT INTO `{table}` ({fields},SchemeId) "
                                    "VALUES ({values},'{scheme_id}')"
                                ).format(
                                    table=person_table_name,
                                    fields=person_fields,
                                    values=person_values,
                                    scheme_id=scheme_id
                                )

                                # 执行sql语句插入数据，record.values()用于获取字段中的值
                                cursor.execute(person_insert_stmt, tuple(person.values()))

                    # 获取审核详细
                    param_datas = {
                        'JsonValues': '{"table":{"tableName":"Pub_Query","row":{"SchemeId":"' + scheme_id + '"}}}',
                        'MethodValue': ''
                    }

                    response_desc = session.post(data_url, data=param_datas, headers=headers, cookies=cookies)

                    desc_data_str = json.loads(json.loads(response_desc.text))
                    desc_data = desc_data_str['datas']['table']['row']['table'][0]['row']
                    print("详细数据：" + str(desc_data))
                    if response_desc.status_code == 200:
                        for desc_record in desc_data:
                            scheme_ship_id = desc_record['SchemeShipId']
                            print("id:"+str(scheme_ship_id))
                            # 不符合清单
                            insertChild(scheme_ship_id, scheme_id, cursor, desc_url, list_table_name)
                            # 问题清单
                            insertChild(scheme_ship_id, scheme_id, cursor, question_url, question_table_name)

            connection.commit()
            print('插入完成!')
    except Exception as e:
        print(f'insert fail::{e}')
        connection.rollback()
        pass
finally:
    # 关闭数据库连接
    connection.close()
